﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.ComponentModel;

namespace AttentenceCheckSystem
{
    class ExcelDbObject
    {
        private OleDbConnection _conn = null;
        private List<string> _sheets = null;

        public List<string> Sheets
        {
            get
            {
                return _sheets;
            }
        }

        public bool Open()
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "Excel files|*.xls*|Excel 2007 files|*.xlsx|Excel 2003 files|*.xls";
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                string strConn = "";
                if (Path.GetExtension(openFileDialog.FileName) == ".xlsx")
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 12.0;HDR=YES\";Data Source=" + openFileDialog.FileName;
                else if (Path.GetExtension(openFileDialog.FileName) == ".xls")
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + openFileDialog.FileName;

                _conn = new OleDbConnection(strConn);
                try
                {
                    _conn.Open();
                    _sheets = new List<string>();
                    DataTable dt = _conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    //dt.Rows
                    foreach (DataRow row in dt.Rows)
                    {
                        string sheetName = row[2].ToString().Trim();
                        if (sheetName[sheetName.Length - 1] == '$')
                            _sheets.Add(row[2].ToString().Trim());
                    }
                    return true;

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    _conn.Close();
                }
            }
            return false;
        }

        public DataTable SelectAllFromTable(string sheetName)
        {
            if (Sheets != null && Sheets.Contains(sheetName))
            {
                try
                {

                    DataSet dataSet = new DataSet();
                    string strCommand = string.Format("SELECT * FROM [{0}]", sheetName);
                    if (_conn.State != ConnectionState.Open)
                        _conn.Open();
                    OleDbDataAdapter adapter = new OleDbDataAdapter(strCommand, _conn);
                    adapter.Fill(dataSet, sheetName);
                    return dataSet.Tables[0];
                }
                catch (Exception ex)
                {
                    return null;
                }
                finally
                {
                    _conn.Close();
                }
            }
            else
                return null;
        }
    }
}
